package dao;


import java.sql.*;
import java.util.Vector;

public class BirdDAO {
	
	//Writes a bird to the database. Same function to write or update.
	public static void writeBird(BirdVO bird) throws SQLException{
		
		//first, tries to write
		
		try{
			PreparedStatement comandoInsert = ConnectionManager.getConnection().prepareStatement("INSERT INTO bird VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
			
			comandoInsert.setString(1, bird.getScientific_name());
			comandoInsert.setString(2, bird.getRisk_of_extinction());
			comandoInsert.setString(3, bird.getImageurl());
			comandoInsert.setFloat(4, bird.getWeight());
			comandoInsert.setString(5, bird.getHabitat());
			comandoInsert.setFloat(6, bird.getIncubationtime());
			comandoInsert.setFloat(7, bird.getLongevity());
			comandoInsert.setString(8, bird.getColor());
			comandoInsert.setFloat(9, bird.getSize());
			comandoInsert.setFloat(10, bird.getClutchsize());
			comandoInsert.setString(11, bird.getDiet());
			
			comandoInsert.executeUpdate();
			//if inserted sucessfully, exit
			return;
		}
		
		catch(SQLException e){
			
		}
		
		
		//tries updating instead of inserting
		PreparedStatement comandoUpdate = ConnectionManager.getConnection().prepareStatement("UPDATE bird SET scientific_name=?, risk_of_extinction=?, imageurl=?, weight=?, habitat=?, incubation_time=?, longevity=?, color=?, size=?, clutchsize=?, diet=? where scientific_name=?");
		comandoUpdate.setString(1, bird.getScientific_name());
		comandoUpdate.setString(2, bird.getRisk_of_extinction());
		comandoUpdate.setString(3, bird.getImageurl());
		comandoUpdate.setFloat(4, bird.getWeight());
		comandoUpdate.setString(5, bird.getHabitat());
		comandoUpdate.setFloat(6, bird.getIncubationtime());
		comandoUpdate.setFloat(7, bird.getLongevity());
		comandoUpdate.setString(8, bird.getColor());
		comandoUpdate.setFloat(9, bird.getSize());
		comandoUpdate.setFloat(10, bird.getClutchsize());
		comandoUpdate.setString(11, bird.getDiet());
		comandoUpdate.setString(12, bird.getScientific_name());
		
		
		comandoUpdate.executeUpdate();
		
			
		
		
			
	}
	
	//Finds a bird by its scientific name
	public static BirdVO findByScientificName(String scientific_name) throws SQLException{
		ResultSet resultados;
		PreparedStatement comandoSelect = ConnectionManager.getConnection().prepareStatement("select * from bird where scientific_name = ?");
		comandoSelect.setString(1, scientific_name);
		resultados = comandoSelect.executeQuery();
		
		resultados.next();
		
		return new BirdVO(resultados.getString(1), resultados.getString(2), resultados.getString(3), resultados.getFloat(4), resultados.getString(5), resultados.getFloat(6), resultados.getFloat(7), resultados.getString(8), resultados.getFloat(9), resultados.getFloat(10), resultados.getString(11));
	}
	
	
	//Runs an arbitrary SQL in the Birds table
	public static Vector<BirdVO> arbitrarySQL(String sql) throws SQLException{
		Vector<BirdVO> birds = new Vector<BirdVO>();
		ResultSet resultados;
		PreparedStatement comandoSelect = ConnectionManager.getConnection().prepareStatement(sql);
		resultados = comandoSelect.executeQuery();
		
		while(resultados.next()){
			birds.add(new BirdVO(resultados.getString(1), resultados.getString(2), resultados.getString(3), resultados.getFloat(4), resultados.getString(5), resultados.getFloat(6), resultados.getFloat(7), resultados.getString(8), resultados.getFloat(9), resultados.getFloat(10), resultados.getString(11)));
		}
		return birds;
	}
	
	//removes a bird from the database
	public static void removeBird(BirdVO bird) throws SQLException{
		PopularNamesDAO.deleteByBird(bird.getScientific_name()); //deletes all references in the popular names table
		CountryDAO.deleteByBird(bird.getScientific_name()); //deletes all references in the country table
		PreparedStatement comandoDelete = ConnectionManager.getConnection().prepareStatement("DELETE FROM bird WHERE scientific_name = ?");
		comandoDelete.setString(1, bird.getScientific_name());
		comandoDelete.executeUpdate();
	}

	//adds a country to the bird
	public static void addCountry(String country, BirdVO bird) throws SQLException{
		CountryDAO.writeCountry(new CountryVO(bird.getScientific_name(), country));
	}
	
	//adds a country to the bird
	public static void addPopularName(String popularName, BirdVO bird) throws SQLException{
		PopularNamesDAO.writePopularName(new PopularNamesVO(popularName, bird.getScientific_name()));
	}
	
	//removes a country from a bird using the country's name
	public static void removeCountry(String country, BirdVO bird) throws SQLException{
		CountryDAO.deleteByCountryAndScientificName(country, bird.getScientific_name());
	}
	
	//removes a country from a bird using the CountryVO object
	public static void removeCountry(CountryVO country, BirdVO bird) throws SQLException{
		CountryDAO.deleteByCountryAndScientificName(country.getCountry(), bird.getScientific_name());
	}
	
	//removes a popular name from a bird using the popular name itself
	public static void removePopularName(String popularName, BirdVO bird) throws SQLException{
		PopularNamesDAO.deleteByScientificNameAndPopularName(popularName, bird.getScientific_name());
	}
	
	//removes a popular name from a bird using the PopularNamesVO object
	public static void removePopularName(PopularNamesVO popularName, BirdVO bird) throws SQLException{
		PopularNamesDAO.deleteByScientificNameAndPopularName(popularName.getName(), bird.getScientific_name());
	}
	
	//returns a list of all popular names a bird has
	public static Vector<PopularNamesVO> listPopularNames(BirdVO bird) throws SQLException{
		return PopularNamesDAO.selectByBird_scientific_name(bird.getScientific_name());
	}
	
	//returns a list of birds given its popular name
	public static Vector<BirdVO> findsAllBirdsByPopularName(String popularName) throws SQLException{
		return arbitrarySQL("SELECT * FROM bird, popular_names where bird.scientific_name = popular_names.bird_scientific_name and popular_names.name = '"+popularName+"'");
	}
	
	//returns a list of birds given a popularNameVO object
	public static Vector<BirdVO> findsAllBirdsByPopularName(PopularNamesVO popularName) throws SQLException{
		return arbitrarySQL("SELECT * FROM bird, popular_names where bird.scientific_name = popular_names.bird_scientific_name and popular_names.name = '"+popularName.getName()+"'");
	}
	
	
	
	
	
	
}
